﻿using System;
using System.Data.Common;
using System.Data.SqlServerCe;
using System.Diagnostics.CodeAnalysis;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using SenseTimeTracker.Misc;
using StedySoft.SenseSDK;
using StedySoft.SenseSDK.DrawingCE;

namespace SenseTimeTracker.BusinessLayer
{
	public class Project : IColumnSortable
	{
		public const string IdColumnName = "projectId";
		public const string ClientIdColumnName = "clientId";
		private const string nameColumnName = "projectName";
		private const string numberColumnName = "projectNumber";
		private const string typeColumnName = "projectType";
		public const string TableName = "Projects";

		private Guid id = Guid.NewGuid();
		private Guid clientId = Guid.Empty;
		private string name = string.Empty;
		private string number = string.Empty;
		private string type = string.Empty;

		public TimeSpan Duration
		{
			get { return TimeSpan.Zero; }
		}

		public DateTime Date
		{
			get { return DateTime.MinValue; }
		}

		public string Description
		{
			get { return string.Empty; }
		}

		public Guid Id
		{
			get { return id; }
			set { id = value; }
		}

		[SuppressMessage("Microsoft.StyleCop.CSharp.ReadabilityRules", "SA1107:CodeMustNotContainMultipleStatementsOnOneLine", Justification = "can contain serialize")]
		public Guid Client
		{
			get { return clientId; }
			set { clientId = value; Persistance.Instance.SelectedClient = value; }
		}

		public string Name
		{
			get { return name; }
			set { name = value; }
		}

		public string Number
		{
			get { return number; }
			set { number = value; }
		}

		public string Type
		{
			get { return type; }
			set { type = value; }
		}

		public static List<Project> Projects
		{
			get
			{
				List<Project> projects = new List<Project>();
				SqlCeCommand command = new SqlCeCommand(
						"SELECT " +
						IdColumnName + ", " +
						ClientIdColumnName + ", " +
						nameColumnName + ", " +
						numberColumnName + ", " +
						typeColumnName +
						" FROM " + TableName,
						DatabaseManager.DbConnection);
				bool shallOpen = DatabaseManager.DbConnection.State != System.Data.ConnectionState.Open;
				if (shallOpen)
				{
					DatabaseManager.DbConnection.Open();
				}

				SqlCeDataReader reader = command.ExecuteReader();
				while (reader.Read())
				{
					projects.Add(new Project
					{
						Id = (Guid)reader[IdColumnName],
						Client = (Guid)reader[ClientIdColumnName],
						Name = reader[nameColumnName].ToString(),
						Number = reader[numberColumnName].ToString(),
						Type = reader[typeColumnName].ToString(),
					});
				}

				if (shallOpen)
				{
					DatabaseManager.DbConnection.Close();
				}

				return projects;
			}
		}

		public static Project GetProject(Guid id)
		{
			Project project = null;
			SqlCeCommand command = new SqlCeCommand(
					"SELECT " +
					ClientIdColumnName + ", " +
					nameColumnName + ", " +
					numberColumnName + ", " +
					typeColumnName +
					" FROM " + TableName +
					" WHERE " + IdColumnName + "=@" + IdColumnName,
					DatabaseManager.DbConnection);
			command.Parameters.Add(IdColumnName, id);
			DatabaseManager.DbConnection.Open();
			SqlCeDataReader reader = command.ExecuteReader();
			if (reader.Read())
			{
				project = new Project
				{
					Id = id,
					Client = (Guid)reader[ClientIdColumnName],
					Name = reader[nameColumnName].ToString(),
					Number = reader[numberColumnName].ToString(),
					Type = reader[typeColumnName].ToString(),
				};
			}

			DatabaseManager.DbConnection.Close();
			return project;
		}

		public void Insert()
		{
			SqlCeCommand command = new SqlCeCommand(
				"INSERT INTO Projects " +
				"(projectId, clientId, projectName, projectNumber, projectType) " +
				"VALUES (@projectId, @clientId, @projectName, @projectNumber, @projectType)",
				DatabaseManager.DbConnection);
			command.Parameters.Add("@projectId", id);
			command.Parameters.Add("@clientId", clientId);
			command.Parameters.Add("@projectName", name);
			command.Parameters.Add("@projectType", type);
			command.Parameters.Add("@projectNumber", number);
			DatabaseManager.DbConnection.Open();
			command.ExecuteScalar();
			DatabaseManager.DbConnection.Close();
		}

		public void Update()
		{
			SqlCeCommand command = new SqlCeCommand(
				"UPDATE Projects " +
				"SET " +
				"projectNumber = @projectNumber, " +
				"projectName = @projectName, " +
				"projectType = @projectType, " +
				"clientId = @clientId " +
				"WHERE projectId = @projectId",
				DatabaseManager.DbConnection);
			command.Parameters.Add("@projectNumber", number);
			command.Parameters.Add("@projectName", name);
			command.Parameters.Add("@projectType", type);
			command.Parameters.Add("@projectId", id);
			command.Parameters.Add("@clientId", clientId);
			DatabaseManager.DbConnection.Open();
			command.ExecuteScalar();
			DatabaseManager.DbConnection.Close();
		}

		public static void Delete(Guid id)
		{
			SqlCeCommand command = new SqlCeCommand(
				"DELETE FROM Projects " +
				"WHERE projectId = @projectId",
				DatabaseManager.DbConnection);
			command.Parameters.Add("@projectId", id);
			DatabaseManager.DbConnection.Open();
			try
			{
				command.ExecuteNonQuery();
			}
			catch
			{
				if (SenseAPIs.SenseMessageBox.Show("Remove all tasks and records \nfor this project also?", "Remove project", SenseMessageBoxButtons.YesNo) == DialogResult.Yes)
				{
					command.CommandText = "DELETE FROM TimeRecords WHERE taskId IN (SELECT taskId FROM Tasks WHERE Tasks.projectId = @projectId)";
					command.ExecuteNonQuery();
					command.CommandText = "DELETE FROM Tasks WHERE projectId = @projectId";
					command.ExecuteNonQuery();
					command.CommandText = "DELETE FROM Projects WHERE projectId = @projectId";
					command.ExecuteNonQuery();
				}
			}

			DatabaseManager.DbConnection.Close();
		}

		public static Guid Copy(Guid oldProjectId, Nullable<Guid> clientId)
		{
			Guid newProjectId = Guid.NewGuid();
			SqlCeCommand command = new SqlCeCommand(
				"INSERT INTO Projects " +
				"(projectId, clientId, projectName, projectNumber, projectType) " +
				"SELECT @newProjectId, " + (clientId.HasValue ? "@" : string.Empty) + "clientId, projectName, projectNumber, projectType " +
				"FROM Projects " +
				"WHERE projectId = @oldProjectId",
				DatabaseManager.DbConnection);
			command.Parameters.Add("@newProjectId", newProjectId);
			command.Parameters.Add("@oldProjectId", oldProjectId);
			if (clientId.HasValue)
			{
				command.Parameters.Add("@clientid", clientId.Value);
			}

			DatabaseManager.DbConnection.Open();
			command.ExecuteScalar();
			DatabaseManager.DbConnection.Close();
			return newProjectId;
		}
	}
}
